package com.example.skillcinema.db.localdb.daos

import androidx.room.Dao
import androidx.room.Delete
import androidx.room.Insert
import androidx.room.OnConflictStrategy
import androidx.room.Query
import com.example.skillcinema.db.localdb.tables.FilmCollectionCrossRef
import com.example.skillcinema.db.localdb.tables.FilmEntity

@Dao
interface FilmCollectionCrossRefDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun addFilmToCollection(crossRef: FilmCollectionCrossRef)

    @Delete
    suspend fun removeFilmFromCollection(crossRef: FilmCollectionCrossRef)

    @Query("DELETE FROM film_collections WHERE film_id = :filmId")
    suspend fun removeFilmFromAllCollections(filmId: Int)

    @Query("DELETE FROM film_collections WHERE collection_id = :collectionId")
    suspend fun clearCollection(collectionId: Int)

    @Query(
        """
        SELECT films.* FROM films
        INNER JOIN film_collections ON films.film_id = film_collections.film_id
        WHERE film_collections.collection_id = :collectionId
    """
    )
    suspend fun getFilmsByCollectionId(collectionId: Int): List<FilmEntity>

    @Query("SELECT * FROM film_collections WHERE film_id = :filmId")
    suspend fun getFilmCollections(filmId: Int): List<FilmCollectionCrossRef>
}